﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;

namespace BLL
{
   public class ClsGiengKhoanBus
    {
       

      public ClsGiengKhoanInfo Info = new ClsGiengKhoanInfo();
        public DataTable TimKiemGK()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.MaGK,TTCHUNG.TenGK,TTCHUNG.SoHieuTL,TTCHUNG.MucNuocTinh,DMTangChuaNuoc.TenTangChuaNuoc,TTCHUNG.NgayKC,TTCHUNG.NgayKT,DMCongTrinh.TenCT,TinhTP.TenTinh,TTCHUNG.ID,TTCHUNG.GhiChu,TinhTP.MaTinh,TinhTP.SoGK 
                              FROM TTCHUNG, DMTangChuaNuoc, DMCongTrinh,TinhTP 
                              WHERE TTCHUNG.TangChuaNNC= DMTangChuaNuoc.MaTangChuaNuoc 
                                    AND TTCHUNG.DeAnBC = DMCongTrinh.MaCT 
                                    AND TTCHUNG.Tinh=TinhTP.MaTinh
                                    AND MaGK like'%" + Info.MaGK +
                                    "%'AND TenGK LIKE'%" + Info.TenGK +
                                    "%'AND Tinh LIKE '%" + Info.Tinh + "%'";          
                          
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }
        public DataTable TimKiemGK1()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.MaGK,TTCHUNG.TenGK
                              FROM TTCHUNG
                              WHERE DeAnBC LIKE '%" + Info.DeAnBC + "%'";

            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }
        public DataTable SelectTinh()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.MaGK,TTCHUNG.TenGK,TTCHUNG.SoHieuTL,TTCHUNG.MucNuocTinh,DMTangChuaNuoc.TenTangChuaNuoc,TTCHUNG.NgayKC,TTCHUNG.NgayKT,DMCongTrinh.TenCT,TinhTP.TenTinh,TTCHUNG.ID,TTCHUNG.GhiChu,TinhTP.MaTinh,TinhTP.SoGK 
                              FROM TTCHUNG, DMTangChuaNuoc, DMCongTrinh,TinhTP 
                              WHERE TTCHUNG.TangChuaNNC= DMTangChuaNuoc.MaTangChuaNuoc 
                                    AND TTCHUNG.DeAnBC = DMCongTrinh.MaCT 
                                    AND TTCHUNG.Tinh=TinhTP.MaTinh
                                    AND Tinh LIKE '%" + Info.Tinh + "%'";

            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }

        public DataTable SelectAll()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.MaGK, TTCHUNG.TenGK,TTCHUNG.SoHieuTL,TTCHUNG.MucNuocTinh,DMTangChuaNuoc.TenTangChuaNuoc,TTCHUNG.NgayKC,TTCHUNG.NgayKT,DMCongTrinh.TenCT,TinhTP.TenTinh,TTCHUNG.ID,TTCHUNG.GhiChu,TinhTP.MaTinh,TinhTP.SoGK 
                                FROM TTCHUNG, DMTangChuaNuoc, DMCongTrinh,TinhTP
                                WHERE TTCHUNG.TangChuaNNC like DMTangChuaNuoc.MaTangChuaNuoc AND TTCHUNG.DeAnBC like DMCongTrinh.MaCT AND TTCHUNG.Tinh like TinhTP.MaTinh";
          
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }

        public DataTable LoadDMTangChuaNuoc()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT * FROM DMTangChuaNuoc";
            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }

        public DataTable LoadDMDeAnBC()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT * FROM DMCongTrinh";
            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }
        public DataTable LoadTinh()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT * FROM TinhTP";
            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }
        public bool Them()
        {
            string strSql = "EXECUTE InsertTTCHUNG'" + Info.MaGK + "', N'" + Info.TenGK + "', '" + Info.SoHieuTL + "', '" + Info.MucNuocTinh + "', '" + Info.TangChuaNNC + "', '" + Info.NgayKC + "', '" + Info.NgayKT + "', '" + Info.DeAnBC + "', '" + Info.Tinh+ "', N'"  +Info.GhiChu + "', '" + Info.ID + "','" + Info.ToaDoX + "','" +  Info.ToaDoY+ "','" + Info.CaoDoZ+"'";
            
            
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Sua()
        {
            string strSql = "EXECUTE UpdateTTCHUNG'" + Info.MaGK + "', N'" + Info.TenGK + "', '" + Info.SoHieuTL + "', '" + Info.MucNuocTinh + "', '" + Info.TangChuaNNC + "', '" + Info.NgayKC + "', '" + Info.NgayKT + "', '" + Info.DeAnBC + "', '" + Info.Tinh + "', N'" + Info.GhiChu + "', '" + Info.ID + "','" + Info.ToaDoX + "','" + Info.ToaDoY + "','" + Info.CaoDoZ + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
        public bool Sua1()
        {
            string strSql = "EXECUTE UpdateGK'" + Info.MaGK + "', N'" + Info.TenGK + "', '" + Info.SoHieuTL + "', '" + Info.MucNuocTinh + "', '" + Info.TangChuaNNC + "', '" + Info.NgayKC + "', '" + Info.NgayKT + "', '" + Info.DeAnBC + "', '" + Info.Tinh + "', N'" + Info.GhiChu + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
       
           public bool xoa()
        {
            string strSql = "EXECUTE DeleteTTCHUNG'" + Info.MaGK +"'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
        public bool Xoa1()
        {
            //string strSql = @"DELETE FROM BomThiNghiem,KTTruocChongOng,ThachLoc,DuLieuMau,OngChongLoc,TTCHUNG WHERE MaGK='" + Info.MaGK + "'";
            string strSql = @"DELETE FROM TTCHUNG WHERE MaGK='" + Info.MaGK + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            if (oConnection.ExecuteNonQuery(strSql) == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
